package easystudy.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import easystudy.dao.MemberDao;
import easystudy.dto.Member;

public class MemberDaoImpl extends JdbcDaoSupport implements MemberDao {

	@Override
	public List<Member> getMemberList(int start, int end) {
		String sql = "select * from "
				+ "(select rownum rnum, num, id, password, name, nickname, birth, email, address, job, field, v_count, grade, state, regdate from"
				+ "(select * from member where grade=0 order by num desc)) "
				+ "where rnum between ? and ?";
		
		Object[] values = new Object[]{
				new Integer(start), new Integer(end)
		};
		
		return getJdbcTemplate().query(sql, values, new MemberRowMapper());
		// TODO Auto-generated method stub
	
	}

	public int getMemberCount(){
		String sql = "select count(*) from member";
		
		return getJdbcTemplate().queryForInt(sql);
	}

	@Override
	public Member getMemberDetail(int member_num) {
		// TODO Auto-generated method stub
		String sql = "select * from member where num=?";
		
		Object[] values = new Object[]{
				new Integer(member_num)
		};
		
		return getJdbcTemplate().queryForObject(sql, values,new MemberRowMapper());
	}

	@Override
	public void updateVisitCount(int member_num, int visitcount) {
		// TODO Auto-generated method stub
		String sql = "update member set v_count =? where num=?";
		
		Object[] values = new Object[]{
				new Integer(visitcount),new Integer(member_num)
		};
		
		getJdbcTemplate().update(sql,values);
	}

	@Override
	public void insertMember(Member member) {
		// TODO Auto-generated method stub
		String sql = "insert into member values"
				+ "(member_num_seq.nextval,?,?,?,?,to_date(?,'YY/MM/DD'),?,?,?,?,0,0,0,sysdate)";
		
		Object[] values = new Object[]{
			member.getId(), member.getPassword(), member.getName(), member.getNickname(),
			member.getBirth(), member.getEmail(), member.getAddress(),
			member.getJob(), member.getField()
		};
		
		getJdbcTemplate().update(sql,values);
	}

	@Override
	public void updateMember(Member member) {
		// TODO Auto-generated method stub
		String sql = "update member set nickname=?, email=?, address=?, job=?, field=? where num=?";
		
		System.out.println(member.getNum());
		
		Object[] values = new Object[]{
				member.getNickname(),
				member.getEmail(),
				member.getAddress(),
				member.getJob(),
				member.getField(),
				member.getNum()
		};
		
		getJdbcTemplate().update(sql,values);

	}


	@Override
	public void updateMemberPass(int member_num, String password) {
		// TODO Auto-generated method stub
		String sql = "update member set password=? where num=?";
		
		Object[] values = new Object[]{
				password,new Integer(member_num)
		};
		
		getJdbcTemplate().update(sql,values);

	}

	@Override
	public void deleteMember(int member_num, int status) {
		// TODO Auto-generated method stub
		String sql = "update member set state =? where num = ?";
		
		Object[] values = new Object[]{
				 new Integer(status), new Integer(member_num)
		};
		
		getJdbcTemplate().update(sql,values);

	}

	@Override
	public int getFavoriteField(int member_num) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Member getMemberDetailById(String id) {
		// TODO Auto-generated method stub
		String sql = "select * from member where id=?";
		
		Object[] values = new Object[]{	new String(id) };
		
		Member member = getJdbcTemplate().queryForObject(sql, values,new MemberRowMapper());
		
		return member;
	}
	
	protected class MemberRowMapper implements RowMapper<Member>{

		@Override
		public Member mapRow(ResultSet rs, int arg1) throws SQLException {
			// TODO Auto-generated method stub
			Member member = new Member();
			member.setNum(rs.getInt("num"));
			member.setId(rs.getString("id"));
			member.setPassword(rs.getString("password"));
			member.setName(rs.getString("name"));
			member.setNickname(rs.getString("nickname"));
			member.setBirth(rs.getString("birth"));
			member.setEmail(rs.getString("email"));
			member.setAddress(rs.getString("address"));
			member.setJob(rs.getString("job"));
			member.setField(rs.getInt("field"));
			member.setV_count(rs.getInt("v_count"));
			member.setGrade(rs.getInt("grade"));
			member.setState(rs.getInt("state"));
			member.setRegdate(rs.getDate("regdate"));
			return member;
		}
		
	}

}
